//<my-matriculator - a simple tool for registering racers in a USAC cyclocross race>
//Copyright (C) <2011>  <Steven Rivers>

//This program is free software: you can redistribute it and/or modify
//it under the terms of the GNU General Public License as published by
//the Free Software Foundation, either version 3 of the License, or
//(at your option) any later version.

//This program is distributed in the hope that it will be useful,
//but WITHOUT ANY WARRANTY; without even the implied warranty of
//MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//GNU General Public License for more details.

//You should have received a copy of the GNU General Public License
//along with this program.  If not, see <http://www.gnu.org/licenses/>.

using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using Registration.Model.Entities;

namespace Registration.Model
{
    class FieldsDb
    {
        public static DataSet GetFieldsDataSet()
        {
            var ds = FieldsDbDataset.ReadDataSet();
            var relFieldCategory = new DataRelation("FieldCategory",
                                                    ds.Tables["Field"].Columns["FieldId"],
                                                    ds.Tables["Categories"].Columns["CategoryField"]);
            ds.Relations.Add(relFieldCategory);
            return ds;
        }


        public static ICollection<Field> GetAllFields()
        {
            var ds = FieldsDbDataset.ReadDataSet();
            var fields = new ObservableCollection<Field>();

            foreach (var f in from DataRow rr in ds.Tables["Field"].Rows 
                              select new Field((int)rr["FieldId"], (string)rr["FieldName"], (bool)rr["FieldOpen"], (int)rr["NextBib"]))
            {
                fields.Add(f);
            }
            return fields;
        }

        public static ICollection<Category> GetAllCategories()
        {
            var ds = FieldsDbDataset.ReadDataSet();
            var cats = new ObservableCollection<Category>();

            foreach (var c in from DataRow catRow in ds.Tables["Categories"].Rows 
                              select new Category((int)catRow["CategoryId"], (string)catRow["CategoryName"], (int)catRow["CategoryField"]))
            {
                cats.Add(c);
            }
            return cats;
        }

        public static IList<Category> GetOpenCategories()
        {
            var fields = GetAllFields();
            var cats = GetAllCategories();
            var matches = from c in cats 
                          join f in fields on c.CategoryField equals f.FieldId 
                          where f.FieldOpen
                          select c;
            return matches.ToList();
        }

        public static ICollection<string> GetCategotyNamesForField(int field)
        {
            var categories = GetAllCategories();
            var matches = from category in categories
                          where category.CategoryField == field
                          select category.CategoryName;

            return new ObservableCollection<string>(matches.ToList());
        }

        public static Category GetCategory(int id)
        {
            Category c = null;
            var ds = FieldsDbDataset.ReadDataSet();
            var table = ds.Tables["Categories"];
            var expression = "CategoryId = " + id.ToString();
            var rows = table.Select(expression);

            if (rows.Length > 0)
            {
                var catRow = rows[0];
                c = new Category((int)catRow["CategoryId"], (string)catRow["CategoryName"], (int)catRow["CategoryField"]);
            }
            return c;
        }

        public static Category FindCategoryWithMap(string name)
        {

            var maps = MappingDb.GetAllMappings();
            var cats = GetAllCategories();

            var matches = from c in cats
                          join m in maps on c.CategoryId equals m.CategoryId
                          where m.RaceName.ToUpper() == name.ToUpper()
                          select c;


            return matches.FirstOrDefault();
        }

        public static Category FindCategory(string name)
        {

            var cats = GetAllCategories();

            var matches = from c in cats
                          where c.CategoryName == name
                          select c;

            return matches.FirstOrDefault();
        }


        public static Field GetField(int id)
        {
            Field f = null;

            var ds = FieldsDbDataset.ReadDataSet();
            var table = ds.Tables["Field"];
            var expression = "FieldId = " + id.ToString();
            var rows = table.Select(expression);

            if (rows.Length > 0)
            {
                var fieldRow = rows[0];
                f = new Field((int)fieldRow["FieldId"], (string)fieldRow["FieldName"], (bool)fieldRow["FieldOpen"], (int)fieldRow["NextBib"]);
            }

            return f;
        }
        
        public static int PreviewNextBib(int catid)
        {
            var cat = GetCategory(catid);
            var field = GetField(cat.CategoryField);
            return field.NextBib;
        }

        public static Field GetFieldUpdateBib(int id)
        {
            Field f = null;
            var ds = FieldsDbDataset.ReadDataSet();
            var table = ds.Tables["Field"];
            var expression = "FieldId = " + id.ToString();
            var rows = table.Select(expression);
            if (rows.Length > 0)
            {
                var fieldRow = rows[0];
                f = new Field((int)fieldRow["FieldId"], (string)fieldRow["FieldName"], (bool)fieldRow["FieldOpen"], (int)fieldRow["NextBib"]);
                fieldRow.BeginEdit();
                fieldRow.SetField("NextBib", f.NextBib+1);
                fieldRow.EndEdit();
                FieldsDbDataset.WriteDataSet(ds);
            }
            return f;
        }

    }
}